﻿using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Reflection;
using System.Windows.Forms;
using System.Xml;

namespace LinxCorrigeNF
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        #region Declaração de variavies 
            /*LOJA_NOTA_FISCAL*/
            string strVBC;
            string strValor_total_itens; //ICMSTot/vProd
            string strDesconto; //ICMSTot/vDESC
            string strEncargo; //ICMSTot/vOutro
            string strFrete;//ICMSTot/vFrete
            string strSeguro; //ICMSTot/vSeg
            public List<String> stringArr = new List<String>();

            /*LOJA_NOTA_FISCAL_ITEM*/
            string strQtde_item; //prod/qCom
            string strValor_item;//prod/vUnCom
            string strPreco_unitario;//prod/vUnTrib
            decimal dPorcentagem_item_rateio;//valor_item /valor_total_itens) * 100
            //string strDesconto_item;//prod/LOJA_VENDA_PRODUTO.DESCONTO_ITEM
            //string strValor_encargos;//prod/vOutro
            //string strValor_descontos;//prod/

            /*LOJA_NOTA_FISCAL_IMPOSTO*/
            /*ICMS*/
            string strPorcICMS00;
            string strVlrImpostoICMS00;
            string strBcImpostoICMS00;
            /*PIS*/
            string strPorcPIS;
            string strVlrImpostoPIS;
            string strBcImpostoPIS;

            /*COFINS*/
            string strPorcCOFINS;
            string strVlrImpostoCOFINS;
            string strBcImpostoCOFINS;

            
            public string ConnectionString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
        #endregion

        #region GetAllNFS
            public DataTable GetAllNFS()
            {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString);
            conn.Open();
            string query = " SELECT A.CODIGO_FILIAL, A.NF_NUMERO, A.SERIE_NF " +
                           " FROM [LOJA_NOTA_FISCAL] A " +
                           " INNER JOIN LOJA_NOTA_FISCAL_XML B " +
                           " ON A.CODIGO_FILIAL = B.CODIGO_FILIAL " +
                           " AND A.NF_NUMERO = B.NF_NUMERO " +
                           " AND A.SERIE_NF = B.SERIE_NF " +
                           " WHERE A.NOTA_CANCELADA = 1 AND A.STATUS_NFE = 5";
            SqlCommand cmd = new SqlCommand(query, conn);

            DataTable dt = new DataTable();
            dt.Load(cmd.ExecuteReader());
            //int totalColumns = dt.Rows.Count;
            conn.Close();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string sCodigoFilial = dt.Rows[i]["CODIGO_FILIAL"].ToString();
                string sNf_numero = dt.Rows[i]["NF_NUMERO"].ToString();
                string sSerie_NF = dt.Rows[i]["SERIE_NF"].ToString();
                //MessageBox.Show("Nota: " + sCodigoFilial + "NF_NUMERO : " + sNf_numero + "Serie_nf : " + sSerie_NF);
                string msg = "Codigo Filial: " + sCodigoFilial + " \nNF_NUMERO : " + sNf_numero + " \nSerie_nf : " + sSerie_NF;
                DialogResult result = MessageBox.Show("Deseja processar a nota abaixo ?\n"+msg, "Processamento de Notas",
                MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (result == DialogResult.Yes)
                {
                    GetXmlDB(sNf_numero.Trim(), sSerie_NF.Trim(), sCodigoFilial.Trim());
                }
                else if (result == DialogResult.No)
                {
                    this.tbXmlFileName.Text = "";
                    return dt;

                }

            }

            return dt;
           
        }
        #endregion

        #region Atualiza notas através de consulta no banco de dados

            public string GetXmlDB(string NF_NUMERO, string serie_nf, string codigo_filial)
            {
                XmlDocument xdoc = new XmlDocument();
                SqlCommand cmd = null;
                string XML = "";
                string sEmissao = string.Empty;
                //SqlTransaction transaction;

                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();

                    try
                    {
                        //transaction = cnn.BeginTransaction();
                        string selectQry = "SELECT cast (XML_NF as xml) FROM [loja_nota_fiscal_xml] WHERE NF_NUMERO = @NF_NUMERO and serie_nf = @serie_nf";
                        cmd = new SqlCommand(selectQry, connection);
                        cmd.Parameters.AddWithValue("@NF_NUMERO", NF_NUMERO);
                        cmd.Parameters.AddWithValue("@serie_nf", serie_nf);
                        cmd.Parameters.AddWithValue("@CODIGO_FILIAL", codigo_filial);
                        XmlReader reader = cmd.ExecuteXmlReader();

                        reader.MoveToContent();
                        DataSet ds = new DataSet();
                        ds.ReadXml(reader);

                        if (ds.Tables.Count == 0)
                        {
                            MessageBox.Show("Não foi encontrado o XML para a NF : " + NF_NUMERO);
                        }
                        else
                        {

                        reader.Close();
                        sEmissao = ds.Tables["ide"].Rows[0]["dhEmi"].ToString().Substring(0, 10);
                        DateTime teste = DateTime.ParseExact(sEmissao, "yyyy-MM-dd",
                                                   System.Globalization.CultureInfo.InvariantCulture);
                        sEmissao = teste.ToString();
                            #region AJUSTE EM LOJA_NOTA_FISCAL
                            SqlDataAdapter Adapter = new SqlDataAdapter();

                            DataTable ICMSTot = ds.Tables["ICMSTot"];
                            for (int i = 0; i < ICMSTot.Rows.Count; i++)
                            {
                                strVBC = ICMSTot.Rows[i]["vBC"].ToString();
                                strValor_total_itens = ICMSTot.Rows[i]["vprod"].ToString();
                                strDesconto = ICMSTot.Rows[i]["vdesc"].ToString();
                                strEncargo = ICMSTot.Rows[i]["vOutro"].ToString();
                                strFrete = ICMSTot.Rows[i]["vfrete"].ToString();
                                strSeguro = ICMSTot.Rows[i]["vseg"].ToString();
                            }
                            Adapter.UpdateCommand = new SqlCommand(" UPDATE LOJA_NOTA_FISCAL SET QTDE_TOTAL = QTDE_CANCELADA, QTDE_CANCELADA = 0," +
                                               " VALOR_TOTAL = VALOR_CANCELADO, VALOR_CANCELADO = 0 ," +
                                               " VALOR_TOTAL_ITENS = @Valor_total_itens, " +
                                               " DESCONTO = @Desconto, ENCARGO = @Encargo, FRETE = @Frete, " +
                                               " SEGURO = @seguro, DATA_CANCELAMENTO = null,  NOTA_CANCELADA = 0 , status_nfe = 5 " +
                                               " WHERE CODIGO_FILIAL = @CODIGO_FILIAL AND NF_NUMERO = @NF_NUMERO AND SERIE_NF = @serie_nf "+
                                               " and qtde_cancelada > 0 ", connection);
                            try
                            {
                                //    using (SqlCommand command = new SqlCommand(comandoNF, cnn))
                                //    {

                                //adapter.UpdateCommand.Parameters.Add("@s_name", OleDbType.VarChar,20,"s_name");
                                Adapter.UpdateCommand.Parameters.Add("@Valor_total", SqlDbType.VarChar).Value = strVBC;
                                Adapter.UpdateCommand.Parameters.Add("@Valor_total_itens", SqlDbType.VarChar).Value = strValor_total_itens;
                                Adapter.UpdateCommand.Parameters.Add("@Desconto", SqlDbType.VarChar).Value = strDesconto;
                                Adapter.UpdateCommand.Parameters.Add("@Encargo", SqlDbType.VarChar).Value = strEncargo;
                                Adapter.UpdateCommand.Parameters.Add("@Frete", SqlDbType.VarChar).Value = strFrete;
                                Adapter.UpdateCommand.Parameters.Add("@seguro", SqlDbType.VarChar).Value = strSeguro;
                                Adapter.UpdateCommand.Parameters.Add("@NF_NUMERO", SqlDbType.VarChar).Value = NF_NUMERO.ToString();
                                Adapter.UpdateCommand.Parameters.Add("@serie_nf", SqlDbType.VarChar).Value = serie_nf.ToString();
                                Adapter.UpdateCommand.Parameters.Add("@CODIGO_FILIAL", SqlDbType.VarChar).Value = codigo_filial.ToString();
                                //Adapter.CommandType = CommandType.Text;
                                Adapter.UpdateCommand.ExecuteNonQuery();
							}	
							catch (SqlException sqlExc)
                            {
                                MessageBox.Show(sqlExc.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                                MessageBox.Show("nVoce deverea atualizar o Status da nota no LinxPOS Manager primeiro !");
                                this.tbXmlFileName.Text = "";
                                return XML; ;
                            }
                            #endregion

                            #region AJUSTE LOJA_NOTA_FISCAL_ITEM
                            int detCount = ds.Tables["det"].Rows.Count;
                            for (int x = 0; x < detCount; x++)
                            {
                                int det_id = Convert.ToInt16(ds.Tables["det"].Rows[x]["det_id"]);
                                string iTemImpressao = Convert.ToString(ds.Tables["det"].Rows[x]["nItem"]).PadLeft(4, '0');

                                strQtde_item = ds.Tables["prod"].Rows[det_id]["qCom"].ToString();
                                strValor_item = ds.Tables["prod"].Rows[det_id]["vUnCom"].ToString();
                                strPreco_unitario = ds.Tables["prod"].Rows[det_id]["vUnTrib"].ToString();
                                dPorcentagem_item_rateio = Decimal.Parse(strValor_item, System.Globalization.CultureInfo.InvariantCulture) /
                                                                Decimal.Parse(strValor_total_itens, System.Globalization.CultureInfo.InvariantCulture) * 100;
                                string strproduto = ds.Tables["prod"].Rows[det_id]["cProd"].ToString();

                                string comandoNFItens = "UPDATE LOJA_NOTA_FISCAL_ITEM  SET QTDE_ITEM = @qtde_item, VALOR_ITEM = @Valor_item, " +
                                " PRECO_UNITARIO = @Preco_unitario , PORCENTAGEM_ITEM_RATEIO = @Porcentagem_item_rateio " +
                                " WHERE CODIGO_FILIAL = @CODIGO_FILIAL AND NF_NUMERO = @NF_NUMERO AND SERIE_NF = @serie_nf " +
                                " and item_impressao = @Item_impressao ";

                                try
                                {
                                    using (SqlCommand command = new SqlCommand(comandoNFItens, connection))
                                    {
                                        command.Parameters.AddWithValue("@qtde_item", strQtde_item);
                                        command.Parameters.AddWithValue("@Valor_item", strValor_item);
                                        command.Parameters.AddWithValue("@Preco_unitario", strPreco_unitario);
                                        command.Parameters.AddWithValue("@Porcentagem_item_rateio", dPorcentagem_item_rateio);
                                        command.Parameters.AddWithValue("@NF_NUMERO", NF_NUMERO);
                                        command.Parameters.AddWithValue("@serie_nf", serie_nf);
                                        command.Parameters.AddWithValue("@CODIGO_FILIAL", codigo_filial);
                                        command.Parameters.AddWithValue("@Item_impressao", iTemImpressao);
                                        command.CommandType = CommandType.Text;
                                        command.ExecuteNonQuery();
                                        //MessageBox.Show("Item: " + iTemImpressao + " Atualizado com sucesso !", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    }
                                }
                                catch (SqlException sqlExc)
                                {
                                    MessageBox.Show(sqlExc.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                                }
                            #endregion

                            #region AJUSTE EM LOJA_NOTA_FISCAL_IMPOSTO
                                if (ds.Tables.Contains("ICMS00"))
                                {

                                    /*ICMS*/
                                    strPorcICMS00 = ds.Tables["ICMS00"].Rows[det_id]["pICMS"].ToString();//ICMS00/pICMS
                                    strVlrImpostoICMS00 = ds.Tables["ICMS00"].Rows[det_id]["vICMS"].ToString();//ICMS00/vICMS
                                    strBcImpostoICMS00 = ds.Tables["ICMS00"].Rows[det_id]["vBC"].ToString();//ICMS00/vBC


                                    string comandoICMS = " UPDATE LOJA_NOTA_FISCAL_IMPOSTO SET TAXA_IMPOSTO = @TaxaImposto , " +
                                                        " VALOR_IMPOSTO = @VlrImpostoICMS, BASE_IMPOSTO = @BcImpostoICMS " +
                                                        " WHERE CODIGO_FILIAL = @CODIGO_FILIAL AND NF_NUMERO = @NF_NUMERO AND SERIE_NF = @serie_nf " +
                                                        " and id_imposto = 1 and item_impressao = @item_impressao";
                                    try
                                    {
                                        using (SqlCommand command = new SqlCommand(comandoICMS, connection))
                                        {
                                            command.Parameters.AddWithValue("@TaxaImposto", strPorcICMS00);
                                            command.Parameters.AddWithValue("@VlrImpostoICMS", strVlrImpostoICMS00);
                                            command.Parameters.AddWithValue("@BcImpostoICMS", strBcImpostoICMS00);
                                            command.Parameters.AddWithValue("@NF_NUMERO", NF_NUMERO);
                                            command.Parameters.AddWithValue("@serie_nf", serie_nf);
                                            command.Parameters.AddWithValue("@CODIGO_FILIAL", codigo_filial);
                                            command.Parameters.AddWithValue("@Item_impressao", iTemImpressao);
                                            command.CommandType = CommandType.Text;
                                            command.ExecuteNonQuery();
                                            //MessageBox.Show("Valor do Imposto ICMS do item: " + iTemImpressao + " atualizado com Sucesso !", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                                        }
                                    }
                                    catch (SqlException sqlExc)
                                    {
                                        MessageBox.Show(sqlExc.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                                    }
                                }

                                /*PIS*/
                                if (ds.Tables.Contains("PISAliq"))
                                {
                                    strPorcPIS = ds.Tables["PISAliq"].Rows[det_id]["pPIS"].ToString();//ICMS00/pICMS
                                    strVlrImpostoPIS = ds.Tables["PISAliq"].Rows[det_id]["vPIS"].ToString();//ICMS00/vICMS
                                    strBcImpostoPIS = ds.Tables["PISAliq"].Rows[det_id]["vBC"].ToString();//ICMS00/vBC

                                    string comandoPIS = " UPDATE LOJA_NOTA_FISCAL_IMPOSTO SET TAXA_IMPOSTO = @TaxaImpostoPIS , " +
                                                        " VALOR_IMPOSTO = @VlrImpostoPIS, BASE_IMPOSTO = @BcImpostoPIS " +
                                                        " WHERE CODIGO_FILIAL = @CODIGO_FILIAL AND NF_NUMERO = @NF_NUMERO AND SERIE_NF = @serie_nf " +
                                                        " and id_imposto = 5 and item_impressao = @item_impressao";
                                    try
                                    {
                                        using (SqlCommand command = new SqlCommand(comandoPIS, connection))
                                        {
                                            command.Parameters.AddWithValue("@TaxaImpostoPIS", strPorcPIS);
                                            command.Parameters.AddWithValue("@VlrImpostoPIS", strVlrImpostoPIS);
                                            command.Parameters.AddWithValue("@BcImpostoPIS", strBcImpostoPIS);
                                            command.Parameters.AddWithValue("@NF_NUMERO", NF_NUMERO);
                                            command.Parameters.AddWithValue("@serie_nf", serie_nf);
                                            command.Parameters.AddWithValue("@CODIGO_FILIAL", codigo_filial);
                                            command.Parameters.AddWithValue("@Item_impressao", iTemImpressao);
                                            command.CommandType = CommandType.Text;
                                            command.ExecuteNonQuery();
                                            //MessageBox.Show("Valor do Imposto PIS do item: " + iTemImpressao + " atualizado com Sucesso !", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                                        }
                                    }
                                    catch (SqlException sqlExc)
                                    {
                                        MessageBox.Show(sqlExc.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                                    }
                                }
                                /*COFINS*/
                                if (ds.Tables.Contains("COFINSAliq"))
                                {
                                    strPorcCOFINS = ds.Tables["COFINSAliq"].Rows[det_id]["pCOFINS"].ToString();//ICMS00/pICMS
                                    strVlrImpostoCOFINS = ds.Tables["COFINSAliq"].Rows[det_id]["vCOFINS"].ToString();//ICMS00/vICMS
                                    strBcImpostoCOFINS = ds.Tables["COFINSAliq"].Rows[det_id]["vBC"].ToString();//ICMS00/vBC


                                    string comandoCOFINS = " UPDATE LOJA_NOTA_FISCAL_IMPOSTO SET TAXA_IMPOSTO = @TaxaImpostoCOFINS, " +
                                                        " VALOR_IMPOSTO = @VlrImpostoCOFINS, BASE_IMPOSTO = @BcImpostoCOFINS " +
                                                        " WHERE CODIGO_FILIAL = @CODIGO_FILIAL AND NF_NUMERO = @NF_NUMERO AND SERIE_NF = @serie_nf " +
                                                        " and id_imposto = 6 and item_impressao = @Item_impressao";
                                    try
                                    {
                                        using (SqlCommand command = new SqlCommand(comandoCOFINS, connection))
                                        {
                                            command.Parameters.AddWithValue("@TaxaImpostoCOFINS", strPorcCOFINS);
                                            command.Parameters.AddWithValue("@VlrImpostoCOFINS", strVlrImpostoCOFINS);
                                            command.Parameters.AddWithValue("@BcImpostoCOFINS", strBcImpostoCOFINS);
                                            command.Parameters.AddWithValue("@NF_NUMERO", NF_NUMERO);
                                            command.Parameters.AddWithValue("@serie_nf", serie_nf);
                                            command.Parameters.AddWithValue("@CODIGO_FILIAL", codigo_filial);
                                            command.Parameters.AddWithValue("@Item_impressao", iTemImpressao);
                                            command.CommandType = CommandType.Text;
                                            command.ExecuteNonQuery();
                                            //MessageBox.Show("Valor do Imposto COFINS do item: " + iTemImpressao + " atualizado com Sucesso !", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                                        }
                                    }
                                    catch (SqlException sqlExc)
                                    {
                                        MessageBox.Show(sqlExc.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                                    }
                                }


                            }
                             #endregion
                           
                            #region AJUSTE DA VENDA
                            CorrigirVendas(codigo_filial, sEmissao.ToString(), NF_NUMERO, serie_nf);
                            #endregion
                        }
                       
                        //transaction.Commit();

                        MessageBox.Show("NF Atualizada com Sucesso", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    catch (SqlException sqlExc)
                    {
                        //transaction.Rollback();
                        MessageBox.Show(sqlExc.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }

                    finally
                    {
                        cmd.Dispose();
                        //cnn.Close();
                    }

                    return XML;
                }
            }
            
        #endregion
        
        #region Atualiza as notas através de arquivo .XML
        public string GetXmlFile()
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                //SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString);

                Int32 lenSequencial = 0;
                string sNf_numero = string.Empty;
                string sSerie_nf = string.Empty;
                string sCodigo_Filial = string.Empty;
                string dValorTotal = string.Empty;
                string sEmissao = string.Empty;
                DataSet dsXmlFile = new DataSet();
                XmlDocument xdoc = new XmlDocument();
                string XML = "";

                XmlReader xmlFile;
                xmlFile = XmlReader.Create(this.tbXmlFileName.Text, new XmlReaderSettings());

                dsXmlFile.ReadXml(xmlFile);

                sEmissao = dsXmlFile.Tables["ide"].Rows[0]["dhEmi"].ToString().Substring(0, 10);
                DateTime teste = DateTime.ParseExact(sEmissao, "yyyy-MM-dd",
                                           System.Globalization.CultureInfo.InvariantCulture);
                sEmissao = teste.ToString();
                dValorTotal = dsXmlFile.Tables["ICMSTot"].Rows[0]["vNF"].ToString();
                sSerie_nf = Convert.ToString(dsXmlFile.Tables["ide"].Rows[0]["serie"]);
                conn.Open();
                string query = " select len(a.SEQUENCIAL)as SEQUENCIAL, b.CODIGO_FILIAL from FATURAMENTO_SEQUENCIAIS a " +
                               " inner join LOJAS_VAREJO b " +
                               " on a.FILIAL = b.FILIAL " +
                               " where a.SERIE_NF = '" + sSerie_nf + "'";
                SqlCommand cmd = new SqlCommand(query, conn);
                var SqlReader = cmd.ExecuteReader();

                if (SqlReader.HasRows)
                {
                    while (SqlReader.Read())
                    {
                        lenSequencial = (Int32)SqlReader.GetInt32(0);
                        sNf_numero = Convert.ToString(dsXmlFile.Tables["ide"].Rows[0]["nNF"]).PadLeft(lenSequencial, '0');
                        sCodigo_Filial = SqlReader.GetString(1);
                    }
                }
                //conn.Close();
                string msg = "Codigo Filial: " + sCodigo_Filial + " \nNF_NUMERO : " + sNf_numero + " \nSerie_nf : " + sSerie_nf + "\nValor Total : " + dValorTotal + "\nEmissao : " + sEmissao;
               


                DialogResult result = MessageBox.Show("Deseja processar a nota abaixo ?\n" + msg, "Processamento de Notas",
                MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (result == DialogResult.Yes)
                {
                    //Caso escolha SIM
                    #region Caso escolha SIM
                    try
                    {
                        using (SqlConnection cnn = new SqlConnection(ConnectionString))
                        {
                            cnn.Open();

                            #region AJUSTE EM LOJA_NOTA_FISCAL
                            SqlDataAdapter Adapter = new SqlDataAdapter();

                            DataTable ICMSTot = dsXmlFile.Tables["ICMSTot"];
                            for (int i = 0; i < ICMSTot.Rows.Count; i++)
                            {
                                strVBC = ICMSTot.Rows[i]["vBC"].ToString();
                                strValor_total_itens = ICMSTot.Rows[i]["vprod"].ToString();
                                strDesconto = ICMSTot.Rows[i]["vdesc"].ToString();
                                strEncargo = ICMSTot.Rows[i]["vOutro"].ToString();
                                strFrete = ICMSTot.Rows[i]["vfrete"].ToString();
                                strSeguro = ICMSTot.Rows[i]["vseg"].ToString();
                            }
                            Adapter.UpdateCommand = new SqlCommand(" UPDATE LOJA_NOTA_FISCAL SET QTDE_TOTAL = QTDE_CANCELADA, QTDE_CANCELADA = 0," +
                                               " VALOR_TOTAL = VALOR_CANCELADO, VALOR_CANCELADO = 0, " +
                                               " VALOR_TOTAL_ITENS = @Valor_total_itens, " +
                                               " DESCONTO = @Desconto, ENCARGO = @Encargo, FRETE = @Frete, " +
                                               " SEGURO = @seguro, DATA_CANCELAMENTO = null,  NOTA_CANCELADA = 0 , status_nfe = 5 " +
                                               " WHERE CODIGO_FILIAL = @CODIGO_FILIAL AND NF_NUMERO = @NF_NUMERO AND SERIE_NF = @serie_nf "+
                                               " and qtde_cancelada > 0 ",cnn);
                            try
                            {
                                //    using (SqlCommand command = new SqlCommand(comandoNF, cnn))
                                //    {

                                //adapter.UpdateCommand.Parameters.Add("@s_name", OleDbType.VarChar,20,"s_name");
                                Adapter.UpdateCommand.Parameters.Add("@Valor_total", SqlDbType.VarChar).Value = strVBC;
                                Adapter.UpdateCommand.Parameters.Add("@Valor_total_itens", SqlDbType.VarChar).Value = strValor_total_itens;
                                Adapter.UpdateCommand.Parameters.Add("@Desconto", SqlDbType.VarChar).Value = strDesconto;
                                Adapter.UpdateCommand.Parameters.Add("@Encargo", SqlDbType.VarChar).Value = strEncargo;
                                Adapter.UpdateCommand.Parameters.Add("@Frete", SqlDbType.VarChar).Value = strFrete;
                                Adapter.UpdateCommand.Parameters.Add("@seguro", SqlDbType.VarChar).Value = strSeguro;
                                Adapter.UpdateCommand.Parameters.Add("@NF_NUMERO", SqlDbType.VarChar).Value = sNf_numero.ToString();
                                Adapter.UpdateCommand.Parameters.Add("@serie_nf", SqlDbType.VarChar).Value = sSerie_nf.ToString();
                                Adapter.UpdateCommand.Parameters.Add("@CODIGO_FILIAL", SqlDbType.VarChar).Value = sCodigo_Filial.ToString();
                                //Adapter.CommandType = CommandType.Text;
                                Adapter.UpdateCommand.ExecuteNonQuery();


                                //command.ExecuteNonQuery();

                                // MessageBox.Show("Cabeçalho da NF Atualizado com Sucesso", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                                //    }
                            }
                            catch (SqlException sqlExc)
                            {
                                MessageBox.Show(sqlExc.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                                MessageBox.Show("nVoce deverea atualizar o Status da nota no LinxPOS Manager primeiro !");
                                this.tbXmlFileName.Text = "";
                                return XML; ;
                            }
                            #endregion

                            #region LOJA_NOTA_FISCAL_ITEM
                            int detCount = dsXmlFile.Tables["det"].Rows.Count;
                            for (int x = 0; x < detCount; x++)
                            {
                                int det_id = Convert.ToInt16(dsXmlFile.Tables["det"].Rows[x]["det_id"]);
                                string iTemImpressao = Convert.ToString(dsXmlFile.Tables["det"].Rows[x]["nItem"]).PadLeft(4, '0');

                                strQtde_item = dsXmlFile.Tables["prod"].Rows[det_id]["qCom"].ToString();
                                strValor_item = dsXmlFile.Tables["prod"].Rows[det_id]["vUnCom"].ToString();
                                strPreco_unitario = dsXmlFile.Tables["prod"].Rows[det_id]["vUnTrib"].ToString();
                                dPorcentagem_item_rateio = Decimal.Parse(strValor_item, System.Globalization.CultureInfo.InvariantCulture) /
                                                                Decimal.Parse(strValor_total_itens, System.Globalization.CultureInfo.InvariantCulture) * 100;
                                string strproduto = dsXmlFile.Tables["prod"].Rows[det_id]["cProd"].ToString();

                                string comandoNFItens = "UPDATE LOJA_NOTA_FISCAL_ITEM  SET QTDE_ITEM = @qtde_item, VALOR_ITEM = @Valor_item, " +
                                " PRECO_UNITARIO = @Preco_unitario , PORCENTAGEM_ITEM_RATEIO = @Porcentagem_item_rateio " +
                                " WHERE CODIGO_FILIAL = @CODIGO_FILIAL AND NF_NUMERO = @NF_NUMERO AND SERIE_NF = @serie_nf " +
                                " and item_impressao = @Item_impressao ";

                                try
                                {
                                    using (SqlCommand command = new SqlCommand(comandoNFItens, cnn))
                                    {
                                        command.Parameters.AddWithValue("@qtde_item", strQtde_item);
                                        command.Parameters.AddWithValue("@Valor_item", strValor_item);
                                        command.Parameters.AddWithValue("@Preco_unitario", strPreco_unitario);
                                        command.Parameters.AddWithValue("@Porcentagem_item_rateio", dPorcentagem_item_rateio);
                                        command.Parameters.AddWithValue("@NF_NUMERO", sNf_numero);
                                        command.Parameters.AddWithValue("@serie_nf", sSerie_nf);
                                        command.Parameters.AddWithValue("@CODIGO_FILIAL", sCodigo_Filial);
                                        command.Parameters.AddWithValue("@Item_impressao", iTemImpressao);
                                        command.CommandType = CommandType.Text;
                                        command.ExecuteNonQuery();
                                        //MessageBox.Show("Item: " + iTemImpressao + " Atualizado com sucesso !", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    }
                                }
                                catch (SqlException sqlExc)
                                {
                                    MessageBox.Show(sqlExc.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                                }
                            #endregion

                            #region AJUSTE EM LOJA_NOTA_FISCAL_IMPOSTO
                                if (dsXmlFile.Tables.Contains("ICMS00"))
                                {

                                    /*ICMS*/
                                    strPorcICMS00 = dsXmlFile.Tables["ICMS00"].Rows[det_id]["pICMS"].ToString();//ICMS00/pICMS
                                    strVlrImpostoICMS00 = dsXmlFile.Tables["ICMS00"].Rows[det_id]["vICMS"].ToString();//ICMS00/vICMS
                                    strBcImpostoICMS00 = dsXmlFile.Tables["ICMS00"].Rows[det_id]["vBC"].ToString();//ICMS00/vBC


                                    string comandoICMS = " UPDATE LOJA_NOTA_FISCAL_IMPOSTO SET TAXA_IMPOSTO = @TaxaImposto , " +
                                                        " VALOR_IMPOSTO = @VlrImpostoICMS, BASE_IMPOSTO = @BcImpostoICMS " +
                                                        " WHERE CODIGO_FILIAL = @CODIGO_FILIAL AND NF_NUMERO = @NF_NUMERO AND SERIE_NF = @serie_nf " +
                                                        " and id_imposto = 1 and item_impressao = @item_impressao";
                                    try
                                    {
                                        using (SqlCommand command = new SqlCommand(comandoICMS, cnn))
                                        {
                                            command.Parameters.AddWithValue("@TaxaImposto", strPorcICMS00);
                                            command.Parameters.AddWithValue("@VlrImpostoICMS", strVlrImpostoICMS00);
                                            command.Parameters.AddWithValue("@BcImpostoICMS", strBcImpostoICMS00);
                                            command.Parameters.AddWithValue("@NF_NUMERO", sNf_numero);
                                            command.Parameters.AddWithValue("@serie_nf", sSerie_nf);
                                            command.Parameters.AddWithValue("@CODIGO_FILIAL", sCodigo_Filial);
                                            command.Parameters.AddWithValue("@Item_impressao", iTemImpressao);
                                            command.CommandType = CommandType.Text;
                                            command.ExecuteNonQuery();
                                            //MessageBox.Show("Valor do Imposto ICMS do item: " + iTemImpressao + " atualizado com Sucesso !", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                                        }
                                    }
                                    catch (SqlException sqlExc)
                                    {
                                        MessageBox.Show(sqlExc.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                                    }
                                }

                                /*PIS*/
                                if (dsXmlFile.Tables.Contains("PISAliq"))
                                {
                                    strPorcPIS = dsXmlFile.Tables["PISAliq"].Rows[det_id]["pPIS"].ToString();//ICMS00/pICMS
                                    strVlrImpostoPIS = dsXmlFile.Tables["PISAliq"].Rows[det_id]["vPIS"].ToString();//ICMS00/vICMS
                                    strBcImpostoPIS = dsXmlFile.Tables["PISAliq"].Rows[det_id]["vBC"].ToString();//ICMS00/vBC

                                    string comandoPIS = " UPDATE LOJA_NOTA_FISCAL_IMPOSTO SET TAXA_IMPOSTO = @TaxaImpostoPIS , " +
                                                        " VALOR_IMPOSTO = @VlrImpostoPIS, BASE_IMPOSTO = @BcImpostoPIS " +
                                                        " WHERE CODIGO_FILIAL = @CODIGO_FILIAL AND NF_NUMERO = @NF_NUMERO AND SERIE_NF = @serie_nf " +
                                                        " and id_imposto = 5 and item_impressao = @item_impressao";
                                    try
                                    {
                                        using (SqlCommand command = new SqlCommand(comandoPIS, cnn))
                                        {
                                            command.Parameters.AddWithValue("@TaxaImpostoPIS", strPorcPIS);
                                            command.Parameters.AddWithValue("@VlrImpostoPIS", strVlrImpostoPIS);
                                            command.Parameters.AddWithValue("@BcImpostoPIS", strBcImpostoPIS);
                                            command.Parameters.AddWithValue("@NF_NUMERO", sNf_numero);
                                            command.Parameters.AddWithValue("@serie_nf", sSerie_nf);
                                            command.Parameters.AddWithValue("@CODIGO_FILIAL", sCodigo_Filial);
                                            command.Parameters.AddWithValue("@Item_impressao", iTemImpressao);
                                            command.CommandType = CommandType.Text;
                                            command.ExecuteNonQuery();
                                            //MessageBox.Show("Valor do Imposto PIS do item: " + iTemImpressao + " atualizado com Sucesso !", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                                        }
                                    }
                                    catch (SqlException sqlExc)
                                    {
                                        MessageBox.Show(sqlExc.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                                    }
                                }
                                /*COFINS*/
                                if (dsXmlFile.Tables.Contains("COFINSAliq"))
                                {
                                    strPorcCOFINS = dsXmlFile.Tables["COFINSAliq"].Rows[det_id]["pCOFINS"].ToString();//ICMS00/pICMS
                                    strVlrImpostoCOFINS = dsXmlFile.Tables["COFINSAliq"].Rows[det_id]["vCOFINS"].ToString();//ICMS00/vICMS
                                    strBcImpostoCOFINS = dsXmlFile.Tables["COFINSAliq"].Rows[det_id]["vBC"].ToString();//ICMS00/vBC


                                    string comandoCOFINS = " UPDATE LOJA_NOTA_FISCAL_IMPOSTO SET TAXA_IMPOSTO = @TaxaImpostoCOFINS, " +
                                                        " VALOR_IMPOSTO = @VlrImpostoCOFINS, BASE_IMPOSTO = @BcImpostoCOFINS " +
                                                        " WHERE CODIGO_FILIAL = @CODIGO_FILIAL AND NF_NUMERO = @NF_NUMERO AND SERIE_NF = @serie_nf " +
                                                        " and id_imposto = 6 and item_impressao = @Item_impressao";
                                    try
                                    {
                                        using (SqlCommand command = new SqlCommand(comandoCOFINS, cnn))
                                        {
                                            command.Parameters.AddWithValue("@TaxaImpostoCOFINS", strPorcCOFINS);
                                            command.Parameters.AddWithValue("@VlrImpostoCOFINS", strVlrImpostoCOFINS);
                                            command.Parameters.AddWithValue("@BcImpostoCOFINS", strBcImpostoCOFINS);
                                            command.Parameters.AddWithValue("@NF_NUMERO", sNf_numero);
                                            command.Parameters.AddWithValue("@serie_nf", sSerie_nf);
                                            command.Parameters.AddWithValue("@CODIGO_FILIAL", sCodigo_Filial);
                                            command.Parameters.AddWithValue("@Item_impressao", iTemImpressao);
                                            command.CommandType = CommandType.Text;
                                            command.ExecuteNonQuery();
                                            //MessageBox.Show("Valor do Imposto COFINS do item: " + iTemImpressao + " atualizado com Sucesso !", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                                        }
                                    }
                                    catch (SqlException sqlExc)
                                    {
                                        MessageBox.Show(sqlExc.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                                    }
                                }


                            }
                                #endregion

                            #region AJUSTE DA VENDA
                            CorrigirVendas(sCodigo_Filial, sEmissao.ToString(), sNf_numero, sSerie_nf);
                            #endregion

                        }

                        MessageBox.Show("NF Atualizada com Sucesso", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);

                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }

                    finally
                    {
                        //cmd.Dispose();
                        //cnn.Close();
                    }

                    return XML;
                    #endregion
                }
                else if (result == DialogResult.No)
                {
                    this.tbXmlFileName.Text = "";
                    return XML;

                }

                return XML;
            }
        }
        #endregion

        #region Método CorrigirVendas
        public void CorrigirVendas(string codigo_filial,string data_venda, string Nf_numero, string serie_nf)
        {

            try
            {
                using (SqlConnection cnn = new SqlConnection(ConnectionString))
                {

                    SqlCommand command = new SqlCommand("SP_RESTAURA_VENDAS", cnn);
                    command.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader;                  
                    command.Parameters.Add(new SqlParameter("@codigo_filial", codigo_filial));
                    command.Parameters.Add(new SqlParameter("@Data_venda", data_venda));
                    command.Parameters.Add(new SqlParameter("@Nf_numero", Nf_numero));
                    command.Parameters.Add(new SqlParameter("@serie_nf", serie_nf));
                    cnn.Open();
                    // execute the command
                    reader = command.ExecuteReader();
                    if (reader.RecordsAffected > 0)
                    {
                        MessageBox.Show("Venda atualizada com sucesso !");
                    }
                    else
                    {
                        MessageBox.Show("Não ha oque atualizar !. \nVerifique os dados informados.");
                    }
                }
            }
            catch (SqlException sqlExc)
            {
                MessageBox.Show(sqlExc.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
           

        }
        #endregion

 

        private void button1_Click(object sender, EventArgs e)
        {
            GetXmlDB(tbNFNUMERO.Text, tbSerieNF.Text, tb_codigo_filial.Text);
        }

        private void tbNFNUMERO_TextChanged(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
            GetAllNFS();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.tbXmlFileName.Text = string.Empty;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                if (!String.Equals(Path.GetExtension(ofd.FileName),
                                   ".xml",
                                   StringComparison.OrdinalIgnoreCase))
                {
                    // Invalid file type selected; display an error.
                    MessageBox.Show("The type of the selected file is not supported by this application. You must select an XML file.",
                                    "Invalid File Type",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Error);

                    // Optionally, force the user to select another file.
                    // ...
                }
                else
                {
                    this.tbXmlFileName.Text = ofd.FileName;
                }
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            GetXmlFile();
            //}
        }

        private void tbXmlFileName_TextChanged(object sender, EventArgs e)
        {
            if (tbXmlFileName.Text != string.Empty)
            {
                GetXmlFile();
            }
        }

        private void button7_Click(object sender, EventArgs e)
        {
            string theDate = string.Empty;
            theDate = dateTimePicker1.Value.ToString("yyyyMMdd");
            if (this.tb_codigo_filial.Text != "")
            {
                CorrigirVendas(tb_codigo_filial.Text, theDate, tbNFNUMERO.Text, tbSerieNF.Text);
            }
            else
            {
                MessageBox.Show("Informe os dados nos campos acima !");
            }
            //string theDate = dateTimePicker1.Value.ToString("yyyyMMdd");
            //MessageBox.Show(theDate);
        }


        private string LoadSQLStatement(string statementName)
        {
          
             
            string sqlStatement = string.Empty;

            string namespacePart = "LinxCorrigeNF.Resources";
            string resourceName = namespacePart + "." + statementName;
            
            var name = this.GetType().Assembly.GetManifestResourceNames();
             
            using (Stream stm = Assembly.GetExecutingAssembly().GetManifestResourceStream(resourceName))
            {
                if (stm != null)
                {
                    sqlStatement = new StreamReader(stm).ReadToEnd();
                }
            }

            return sqlStatement;
        }
        
        
        private void Form1_Load(object sender, EventArgs e)
        {

            try
            {
                using (SqlConnection cnn = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(LoadSQLStatement("SP_RESTAURA_VENDAS.sql"), cnn);
                    command.CommandType = CommandType.Text;
                    cnn.Open();
                    command.ExecuteReader();
                }
            }
            catch (SqlException sqlExc)
            {
                MessageBox.Show(sqlExc.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
           
        }

        private void button5_Click(object sender, EventArgs e)
        {
            GetXmlDB(tbNFNUMERO.Text, tbSerieNF.Text, tb_codigo_filial.Text);
        }

      
    }
}
